package com.lm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.lm.dao.DiscountDAO;
import com.lm.model.Discount;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;

public class DiscountDAOImpl implements DiscountDAO{
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void addDiscount(Discount discount) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" insert into lm_discount(discountType,disMount,disPercent,");
		sb.append(" disBid,productID)");
		sb.append(" values(").append(discount.getDiscountType());
		sb.append(",").append(discount.getDisMount());
		sb.append(",").append(discount.getDisPercent());
		sb.append(",").append(discount.getDisBid());
		sb.append(",").append(discount.getProductId()).append(")");
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editDiscount(Discount discount, String discountID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_discount set discountType=").append(
				discount.getDiscountType());
		sb.append(",disMount=").append(discount.getDisMount());
		sb.append(",disPercent=").append(discount.getDisPercent());
		sb.append(",disBid=").append(discount.getDisBid());
		sb.append(",productID=").append(discount.getProductId());
		sb.append(" where id=").append(Integer.parseInt(discountID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public void editDiscount(String mount, String productID, double totalPrice,
			double totalDisBid) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_discount set mount=").append(
				Integer.parseInt(mount));
		sb.append(",totalPrice=").append(totalPrice);
		sb.append(",totalDisBid=").append(totalDisBid);
		sb.append(" where productID=").append(Integer.parseInt(productID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public List getAllDiscountInfo() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_discount  ");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());

			while (rs.next()) {
				Discount dis = new Discount();
				dis.setDisBid(rs.getDouble("disBid"));
				dis.setDiscountType(rs.getInt("discountType"));
				dis.setDisMount(rs.getInt("disMount"));
				dis.setDisPercent(rs.getInt("disPercent"));
				dis.setId(rs.getInt("id"));
				dis.setProductId(rs.getInt("productID"));

				resList.add(dis);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public Discount getDiscountInfoByProductID(String productID)
			throws Throwable {
		Discount ld = new Discount();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_discount  ");
		sb.append("  where productID=").append(Integer.parseInt(productID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				ld.setDisBid(rs.getDouble("disBid"));
				ld.setDiscountType(rs.getInt("discountType"));
				ld.setDisMount(rs.getInt("disMount"));
				ld.setDisPercent(rs.getInt("disPercent"));
				ld.setId(rs.getInt("id"));
				ld.setProductId(rs.getInt("productID"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return ld;
	}

	public boolean getDiscountByTypeIDAndBrandID(String typeID, String brandID)
			throws Throwable {
		boolean flag = false;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("  select * from lm_discount ");
		sb.append("  where productID in ( ");
		sb.append("  select id from lm_product");
		sb.append("  where typeID=").append(Integer.parseInt(typeID));
		sb.append("  and brandID=").append(Integer.parseInt(brandID));
		sb.append("  )");
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			rs.last();

			if (rs.getRow() != 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return flag;
	}

	public void deleteDiscountByTypeIDAndBrandID(String typeID, String brandID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("delete from lm_discount ");
		sb.append("where productID in (");
		sb.append("select id from lm_product ");
		sb.append("where typeID=").append(Integer.parseInt(typeID));
		sb.append(" and brandID=").append(Integer.parseInt(brandID));
		sb.append(")");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteDiscountByProductID(int proID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("  delete from lm_discount ");
		sb.append("  where productID=").append(proID);
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
